package com.sdy.model;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jfinal.aop.Before;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.sdy.dto.EmployerDto;
/**
mysql> desc employer;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| name          | varchar(64)  | YES  |     | NULL    |                |
| departmentId  | int(11)      | YES  |     | NULL    |                |
| username      | varchar(32)  | YES  |     | NULL    |                |
| password      | varchar(128) | YES  |     | NULL    |                |
| lastLoginDate | datetime     | YES  |     | NULL    |                |
| loginTimes    | int(11)      | YES  |     | NULL    |                |
| position      | varchar(100) | YES  |     | NULL    |                |
| empId         | varchar(100) | YES  |     | NULL    |                |
| birthdate     | datetime     | YES  |     | NULL    |                |
| email         | varchar(60)  | YES  |     | NULL    |                |
| sex           | int(4)       | YES  |     | NULL    |                |
| joinTime      | datetime     | YES  |     | NULL    |                |
| address       | varchar(100) | YES  |     | NULL    |                |
| phone         | varchar(20)  | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
 * */
@SuppressWarnings("serial")
public class Employer extends Model<Employer> {

	public static final Employer dao = new Employer();
	
	public Employer login(String username,String password){
		Employer user = this.findFirst("select * from employer where username=? and password=?", username,password);
		return user;
	}
	
	public Map<String, Object> datagrid(int page, int rows) throws Exception {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Employer> employerList = null;
		Page<Employer> employerPage = this.paginate(page, rows, "SELECT employer.*, department.`name` AS departmentName,position.`name` AS positionName  ","FROM employer LEFT JOIN department ON employer.`departmentId` = department.`id` LEFT JOIN position ON position.`id` = employer.`position`");
		if(employerPage != null)
			employerList = employerPage.getList();
		resultMap.put("rows", employerList);
		resultMap.put("total", this.find("select count(*) AS total from employer").get(0).getLong("total"));
		return resultMap;
	}

	public Record getById(int id) {
		List<Record> recordList = Db.find("SELECT "
				+" emp.`id` 'employer.id',"
				+" emp.`empId` 'employer.empId',"
				+" headImg.`id` 'headImg.id',"
				+" headImg.`dataType` 'headImg.dataType',"
				+" headImg.`textValue` 'headImg.textValue',"
				+" headImg.`seat` 'headImg.seat',"
				+" headImg.`modelId` 'headImg.modeId',"
				+" headImg.`downloadTimes` 'headImg.downloadTimes',"
				+" headImg.`watchTimes` 'headImg.watchTimes',"
				+" headImg.`picUrl` 'headImg.picUrl',"
				+" headImg.`imageText` 'headImg.imageText',"
				+" headImg.`owner` 'headImg.owner',"
				+" bigImg.`id` 'bigImg.id',"
				+" bigImg.`dataType` 'bigImg.dataType',"
				+" bigImg.`textValue` 'bigImg.textValue',"
				+" bigImg.`seat` 'bigImg.seat',"
				+" bigImg.`modelId` 'bigImg.modeId',"
				+" bigImg.`downloadTimes` 'bigImg.downloadTimes',"
				+" bigImg.`watchTimes` 'bigImg.watchTimes',"
				+" bigImg.`picUrl` 'bigImg.picUrl',"
				+" bigImg.`imageText` 'bigImg.imageText',"
				+" bigImg.`owner` 'bigImg.owner',"
				+" emp.`name` 'employer.name',"
				+" emp.`birthdate` 'employer.birthdate',"
				+" emp.`sex` 'employer.sex',"
				+" emp.`phone` 'employer.phone',"
				+" emp.`email` 'employer.email',"
				+" emp.`departmentId` 'employer.departmentId',"
				+" emp.`joinTime` 'employer.joinTime',"
				+" emp.`username` 'employer.username',"
				+" emp.`position` 'employer.position',"
				+" info.`id` 'info.id',"
				+" info.`dataType` 'info.dataType',"
				+" info.`textValue` 'info.textValue',"
				+" info.`seat` 'info.seat',"
				+" info.`modelId` 'info.modeId',"
				+" info.`downloadTimes` 'info.downloadTimes',"
				+" info.`watchTimes` 'info.watchTimes',"
				+" info.`picUrl` 'info.picUrl',"
				+" info.`imageText` 'info.imageText',"
				+" info.`owner` 'info.owner'"
				+" FROM"
				+" employer emp "
				+" LEFT JOIN modelinfo headImg "
				+" ON headImg.`owner` = emp.`empId` "
				+" AND headImg.`seat` = 'headImg' "
				+" LEFT JOIN modelinfo bigImg "
				+" ON bigImg.`owner` = emp.`empId` "
				+" AND bigImg.`seat` = 'bigImg' "
				+" LEFT JOIN modelinfo info "
				+" ON info.`owner` = emp.`empId` "
				+" AND info.`seat` = 'info' "
				+" WHERE emp.`id` = ?",id);
		return recordList.get(0);
	}
	
	public List<Record> searchEmployerDtoByDeptId(int deptId) throws Exception {
		List<Record> recordList = null;
		String sql = "select " + 
				  "emp.`empId`, " + 
				  "head.`picUrl` 'headPic', " + 
				  "emp.`name`, " + 
				  "position.`name` 'positionName', " + 
				  "dept.`name` 'departmentName', " + 
				  "big.`picUrl` 'bigPic', " + 
				  "info.`textValue` 'info' " + 
				"from " + 
				  "employer emp  " + 
				  "left join position " + 
				    "on position.`id` = emp.`position`  " + 
				  "left join modelinfo head  " + 
				    "on head.`owner` = emp.`empId`  " + 
				    "and head.`seat` = 'headImg'  " + 
				  "left join modelinfo big  " + 
				    "on big.`owner` = emp.`empId`  " + 
				    "and big.`seat` = 'bigImg'  " + 
				  "left join modelinfo info  " + 
				    "on info.`owner` = emp.`empId`  " + 
				    "and info.`seat` = 'info'  " + 
				  "left join department dept  " + 
				    "on emp.`departmentId` = dept.`id`  " + 
				"where departmentId = ?  " + 
				"order by position";
		recordList = Db.find(sql, deptId);
		return recordList;
	}
	
	/**
	 * 根据ip和userId获取用户信息
	 * */
	public Employer getEmployerByIpAndUserId(String username, String ip) throws Exception {
		List<Employer> list = this.find("select * from employer where username = ? and ip = ?", username, ip);
		if(list.size()>1) {
			throw new IllegalArgumentException("用户异常！");
		}
		return list.get(0);
	}
}
